# encoding: utf-8
# @author: w4dll
# @file: main_pane.py
# @time: 2020/4/23 19:41
import os
from mytool import *
from paperset_pane import PapersetPane
from resource.mainUI import Ui_MainWindow
from resource.tk_add import Ui_Dialog
from moni_pane import moniPane
from test_pane import testPane
from papermanage_pane import paperManagePane
from usermanage_pane import userManegePane
from tk_guanli_pane import tkGuanli
from score_tongji_pane import scoreTongji
from addtestpaper_pane import AddPaperTest
import time
from shutil import copy

import locale
locale.setlocale(locale.LC_ALL, 'en')

class MainPane(QMainWindow, Ui_MainWindow):
    def __init__(self, parent=None, *args, **kwargs):
        super().__init__(parent, *args, **kwargs)
        self.setupUi(self)

        self.db = sqlite3Class("data.db")

        # 定义model
        self.list_model = QStandardItemModel()
        self.listView.setModel(self.list_model)
        self.listView.setSelectionBehavior(QAbstractItemView.SelectRows)  # 设置表格的选取方式是行选取

        self.list_model2 = QStandardItemModel()
        self.listView_2.setModel(self.list_model2)
        self.listView_2.setSelectionBehavior(QAbstractItemView.SelectRows)

        self.list_model3 = QStandardItemModel()
        self.listView_3.setModel(self.list_model3)
        self.listView_3.setSelectionBehavior(QAbstractItemView.SelectRows)

        self.list_model4 = QStandardItemModel()

        # 初始化菜单
        self.initSlot()


    # 初始化信号-槽连接
    def initSlot(self):
        """
        初始化信号与槽函数，将主界面菜单按钮与相关函数进行了链接
        """
        self.action_tianjiashijuan.triggered.connect(self.paperset)         # 试卷添加
        self.action_shijuanguanli.triggered.connect(self.papermanagement)   # 试卷管理
        self.actiond_qingkongshijuan.triggered.connect(self.clearSJ)        # 清空试卷

        self.action_daorutiku.triggered.connect(self.importTk)              # 导入Excel题库
        self.action_daochutiku.triggered.connect(self.tk_output)            # 导出题库到Excel文件中
        self.action_qingkongtiku.triggered.connect(self.tk_clear)           # 清空题库
        self.action_tikuguanli.triggered.connect(self.tk_guanli)            # 题库管理

        self.action_kaishikaoshi.triggered.connect(self.on_start)           # 开始考试
        self.action_suiji_test.triggered.connect(self.suiji_test)           # 随机考试
        self.action_daoru.triggered.connect(self.importExamnationPaperFromJsonFile)     # 导入试卷
        self.action_daochu.triggered.connect(self.exportExamnationPaperToJsonFile)      # 导出试卷

        self.actionuser_guanli.triggered.connect(self.usermanage)           # 用户管理
        self.actionuser_daochu.triggered.connect(self.userOutput)           # 导出用户信息
        self.actionuser_daoru.triggered.connect(self.userImport)            # 导入用户信息

        self.action_chengjitongji.triggered.connect(self.socre_tongji)      # 成绩统计

        self.action_close.triggered.connect(self.closeMainUi)                   # 关闭主界面
        self.action_outputdata.triggered.connect(self.exportDBData)             # 导出数据
        self.action_importdata.triggered.connect(self.importDBData)             # 导入数据

        self.action_tiku_moban.triggered.connect(self.createExcelSubjectLibraryTemplate)       # 生成题库模板

        # 初始化列表
        self.initList()

    # def subjectLibraryManage(self):
    #     """
    #     管理题库
    #     """
    #     tiku = self.db.selectdata("select * from t_timu")
    #     if len(tiku) == 0:
    #         QMessageBox.about(self, "提示", "请先导入题库！")
    #     else:
    #         self.win_subjectlibrary = SubjectLibraryManagement()
    #         self.win_subjectlibrary.setWindowModality(Qt.ApplicationModal)
    #         self.win_subjectlibrary.show()

    def exportExamnationPaperToJsonFile(self):
        """
        导出试卷信息到json数据，此处不仅导出试卷信息，还包含试卷对应的试题，避免重新导入时题号变化带来试卷不一致
        :return:
        """
        filepath, _ = QFileDialog.getSaveFileName(self, "请选择导出路径", "./", "json(*.json)")
        if filepath == "":
            return
        tests = self.db.selectdata("select * from t_shijuan")
        ls_all_shijuan = []
        for item in tests:
            timu = str(item[6]).split(",")
            list_ti = []  # 存储所有题号对应的题目
            for ti in timu:
                ddd = self.db.selectdata("select * from t_timu where ID={}".format(ti))
                if len(ddd[0]) > 0:
                    list_ti.append(ddd)
            list_ti.append(item)  # 存入试卷信息，放在最后
            ls_all_shijuan.append(list_ti)
        writejson(filepath, ls_all_shijuan)

    def importExamnationPaperFromJsonFile(self):
        """
        导入试卷json数据,导入的数据格式为[[试卷1], [试卷2]...],其中试卷1格式为:[(题1),(题2)...(试卷信息)]
        :return:None
        """
        filepath, _ = QFileDialog.getOpenFileName(self, "请选择导出路径", "./", "json(*.json)")
        if filepath == "":
            return
        try:
            data = readjson(filepath)
            for item in data:
                list_tihao = []
                for x in item[:-1]:
                    tmp = self.db.selectdata("select ID from t_timu where 题干='{}' and 题型='{}'".format(x[0][3], x[0][4]))
                    if len(tmp) > 0:
                        list_tihao.append(str(tmp[0][0]))
                    else:
                        sql = "insert into t_timu(适用人员, 专业分类, 题干, 题型,选项1, 选项2, 选项3, 选项4, 选项5, " \
                              "选项6, 选项7, 选项8,选项9,选项10,答案,备注) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
                        self.db.insert_singlerow_data(sql, x[0][1:])
                        tmp1 = self.db.selectdata("select max(ID) from t_timu")
                        list_tihao.append(str(tmp1[0][0]))
                val = item[-1][1:6] + [",".join(list_tihao), item[-1][7], item[-1][8], item[-1][9]]
                sql = "insert into t_shijuan(适用人员,专业分类,试卷名称,题型数量,题型分值,题号集,创建时间,考试时长,题目来源) values(?,?,?,?,?,?,?,?,?)"
                self.db.insert_singlerow_data(sql, val)
            self.initList()
        except Exception as e:
            print(e)
            QMessageBox.about(self, "提示", "导入失败！")

    def createExcelSubjectLibraryTemplate(self):
        """
        生成题库Excel模板文件，便于用户导入题库
        """
        filepath, _ = QFileDialog.getSaveFileName(self, "请选择模板存储路径", "./", "excel(*.xlsx)")
        if len(filepath) > 0:
            wb = workbook.Workbook()
            # todo 这里要不要把使用人员、专业分类直接写进去，便于后期导入
            first_row = ["适用人员", "专业分类", "题干", "题型", "选项A", "选项B", "选项C", "选项D", "选项E", "选项F", "选项G", "选项H", "选项I", "选项J", "答案", "备注"]
            first_row1 = ["所有人员", "语文", "谁是最可爱的人？", "单选题", "解放军叔叔", "车夫", "商贩", "小偷", "", "", "", "", "", "", "A", ""]
            first_row2 = ["所有人员", "语文", "谁是最可爱的人们？", "多选题", "解放军叔叔", "车夫", "商贩", "小偷", "学生", "哥哥", "", "", "", "", "AB", ""]
            first_row3 = ["所有人员", "语文", "我最可爱的人们？", "判断题", "对", "错", "", "", "", "", "", "", "", "", "A", ""]
            first_row4 = ["所有人员", "语文", "我最爱(   )？", "填空题", "", "", "", "", "", "", "", "", "", "", "妈妈", ""]
            first_row5 = ["所有人员", "语文", "请简述火箭的发射原理？", "问答题", "", "", "", "", "", "", "", "", "", "", "嘟嘟嘟嘟嘟嘟嘟嘟嘟嘟嘟嘟嘟嘟，嘭！", ""]
            wb.worksheets[0].append(first_row)
            wb.worksheets[0].append(first_row1)
            wb.worksheets[0].append(first_row2)
            wb.worksheets[0].append(first_row3)
            wb.worksheets[0].append(first_row4)
            wb.worksheets[0].append(first_row5)
            wb.save(filepath)
            QMessageBox.about(self, "提示", "生成成功！")

    def exportDBData(self):
        """
        直接复制数据库文件到指定的路径下实现所有文件的导出
        :return:
        """
        filepath, _ = QFileDialog.getSaveFileName(self, "导出数据库数据", "./", "db(*.db)")
        if filepath == "":
            return
        try:
            copy("./data.db", filepath)
            QMessageBox.about(self, "提示", "导出成功！")
        except Exception as e:
            print(e)

    def importDBData(self):
        """
        导入db数据库中的数据,包括题库数据，试卷数据，考试数据，用户数据
        :return: None
        """
        filepath, _ = QFileDialog.getOpenFileName(self, "导入数据", "./", "db数据库文件(*.db)")
        if filepath == "":
            return
        try:
            db = sqlite3Class(filepath)

            # 用户数据导入
            userdata = db.selectdata("select * from t_userinfo")
            for x in userdata:
                tmp = self.db.selectdata("select * from t_userinfo where 用户名='"+x[1]+"'")
                if len(tmp) == 0:
                    self.db.insert_singlerow_data("insert into t_userinfo(用户名,密码,姓名,性别,一级单位,二级单位,三级单位,用户权限,注册时间) "
                                                  "values(?,?,?,?,?,?,?,?,?)", x[1:])
                else:
                    if QMessageBox.Yes == QMessageBox.question(self, "提示", "用户【"+str(tmp[0][1])+"】已存在，是否覆盖？"):
                        self.db.excuteSql("delete from t_userinfo where 用户名='"+str(tmp[0][1])+"'")
                        self.db.insert_singlerow_data("insert into t_userinfo(用户名,密码,姓名,性别,一级单位,二级单位,三级单位,用户权限,注册时间) "
                                                      "values(?,?,?,?,?,?,?,?,?)", x[1:])
                        # todo 这里可以做自己的控件提示框架
            # 题库数据导入
            tikudata = db.selectdata("select * from t_timu")
            for x in tikudata:
                tmp = self.db.selectdata("select * from t_timu where 题干='" + x[3] + "' and 题型='"+x[4]+"'")
                if len(tmp) == 0:
                    sql = "insert into t_timu(适用人员, 专业分类, 题干, 题型,选项1, 选项2, 选项3, 选项4, 选项5, " \
                          "选项6, 选项7, 选项8,选项9,选项10,答案,备注) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
                    self.db.insert_singlerow_data(sql, x[1:])

            # 试卷导入
            examnationPaper = db.selectdata("select * from t_shijuan")
            ls = ["单选题", "多选题", "判断题", "填空题", "问答题"]
            for item3 in examnationPaper:
                if len(item3) > 0:
                    tmp = self.db.selectdata("select * from t_shijuan where 试卷名称='{}' and 创建时间='{}'".format(item3[3], item3[7]))
                    if tmp != []:
                        continue
                    else:
                        subjectCountList = ["0", "0", "0", "0", "0"]
                        subjectNumCollectionList = []

                        currentSubjectNumCollection = str(item3[6]).split(",")
                        for subjectNum in currentSubjectNumCollection:
                            subjectRow = db.selectdata("select * from t_timu where ID='{}'".format(subjectNum))
                            new_row = self.db.selectdata("select * from t_timu where 题干='{}' and 题型='{}'".format(subjectRow[0][3], subjectRow[0][4]))
                            if len(new_row) > 0:
                                subjectNumCollectionList.append(str(new_row[0][0]))
                                subjectCountList[ls.index(new_row[0][4])] = str(int(subjectCountList[ls.index(new_row[0][4])]) + 1)
                        subjectNumCollectionStr = ",".join(subjectNumCollectionList)
                        print(subjectNumCollectionStr)
                        subjectCountStr = ",".join(subjectCountList)
                        examnationInfo = item3[1:4] + (subjectCountStr, item3[5], subjectNumCollectionStr)+item3[7:]
                        sql = "insert into t_shijuan(适用人员,专业分类,试卷名称,题型数量,题型分值,题号集,创建时间,考试时长,题目来源) values(?,?,?,?,?,?,?,?,?)"
                        self.db.insert_singlerow_data(sql, examnationInfo)
        except Exception as e:
            print(e)
        self.initList()

    def closeMainUi(self):
        """
        关闭用户主界面，即退出
        """
        self.close()

    def setuserIcon(self):
        """
        设置用户图标，根据用户权限进行设置，权限1表示一般用户，灰色图标，权限小于10，表示管理员，蓝色图标，权限大于10，超级管理员，黄色图标
        """
        p = self.userinfo[0][8]
        if p >= 10:
            self.label_picture.setStyleSheet("border-image:url(:/login/images/超级管理员.png);")
        elif 1 < p < 10:
                self.label_picture.setStyleSheet("border-image:url(:/login/images/管理员.png);")
        else:
            self.label_picture.setStyleSheet("border-image:url(:/login/images/一般用户.png);")

    # todo 生成试卷按钮
    def get_testpaper(self):
        dd = self.getExamnationDataFromList3()
        if len(dd) > 0:
            self.win_getpaper = AddPaperTest(dd)
            self.win_getpaper.add_test_signal.connect(self.addpaperfinished)    # 添加成功，刷新列表
            self.win_getpaper.setWindowModality(Qt.ApplicationModal)
            self.win_getpaper.show()
        else:
            QMessageBox.about(self, "提示", "请先添加题库！")

    def addpaperfinished(self):
        self.frushlist4()

        s = []
        for i in range(self.list_model3.rowCount()):
            s.append(str(self.list_model3.data(self.list_model3.index(i, 0))))
        id = self.db.selectdata("select max(ID) from t_shijuan")[0][0]
        sql = "update t_shijuan set 题目来源='{}' where ID ={}".format(",".join(s), id)
        self.db.excuteSql(sql)

    # 统计成绩菜单
    def socre_tongji(self):
        self.win_tongji = scoreTongji()
        self.win_tongji.setWindowModality(Qt.ApplicationModal)
        self.win_tongji.show()

    # todo list4行选中
    def row_select4(self):
        rowindex = self.tableView.selectionModel().selection().indexes()
        s = self.list_model4.data(rowindex[1])
        self.label_14.setText(s)
        self.pushButton_5.setEnabled(True)
        self.pushButton_7.setEnabled(True)

        # todo 这里写入试卷信息，用其他方式
        # 考试信息写入默认试卷
        self.db.excuteSql("update t_setting set testinfo='"+str(self.list_model4.data(rowindex[0]))+"' where ID='1'")
        self.setdefautinfo()

    # todo 随机考试按钮
    def suiji_test(self):
        data = self.db.selectdata("select testinfo from t_setting")
        if data[0][0] == "" or data[0][0] is None:
            QMessageBox.about(self, "提示", "请先添加试卷！")
        else:
            dt = self.db.selectdata("select * from t_shijuan where ID='{}'".format(data[0][0]))
            if len(dt) > 0:
                self.startTest(dt, "随机考试")
            else:
                QMessageBox.about(self, "提示", "试卷不存在或已删除，请重新添加！")

    # 开始考试按钮
    def start_test4(self):
        self.on_start()

    # todo 清空题库1
    def tk_clear(self):
        if QMessageBox.Yes == QMessageBox.question(self, "提示", "是否清空题库，清空后不可恢复！"):
            self.db.excuteSql("delete from t_timu")
            self.db.excuteSql("delete from t_shijuan")
            self.initList()

    # todo 题库管理
    def tk_guanli(self):
        # 显示题库，可以进行修改，删除等操作
        tiku = self.db.selectdata("select * from t_timu")
        if len(tiku) == 0:
            QMessageBox.about(self, "提示", "请先导入题库！")
        else:
            self.win_tk_guanli = tkGuanli()
            self.win_tk_guanli.setWindowTitle("题目管理")
            self.win_tk_guanli.setWindowModality(Qt.ApplicationModal)
            self.win_tk_guanli.show()

    # todo 初始化list,适用人员列表
    def initList(self):
        self.list_model.clear()
        shiyongrenyuan = self.db.selectdata("select distinct 适用人员 from t_timu")
        list_item = []
        for x in shiyongrenyuan:
            list_item.append(QStandardItem(x[0]))
        self.list_model.appendColumn(list_item)
        self.frushlist4()

    def frushlist4(self):
        """
        刷新list4列表
        """
        self.list_model4.clear()  # todo 注意这里会清空标题列，所以后面要重新添加，不然报错！
        data = self.db.selectdata("select * from t_shijuan")
        for x in data:
            q0 = QStandardItem(str(x[0]))
            q0.setTextAlignment(Qt.AlignCenter)  # 设置单元格居中显示
            q1 = QStandardItem(str(x[3]))
            q1.setTextAlignment(Qt.AlignCenter)  # 设置单元格居中显示
            q2 = QStandardItem(str(x[8]))
            q2.setTextAlignment(Qt.AlignCenter)

            nums = str(x[4]).split(",")
            vals = str(x[5]).split(",")
            sums, scores = 0, 0
            for i in range(5):
                sums += int(nums[i])
                scores += int(nums[i])*int(vals[i])
            q3 = QStandardItem(str(sums))
            q3.setTextAlignment(Qt.AlignCenter)
            q4 = QStandardItem(str(scores))
            q4.setTextAlignment(Qt.AlignCenter)
            self.list_model4.appendRow([q0, q1, q2, q3, q4])
        self.tableView.setModel(self.list_model4)
        self.tableView.horizontalHeader().setStretchLastSection(True)
        # self.tableView.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)           # 所有列都扩展自适应宽度，填充充满整个屏幕宽度
        self.tableView.horizontalHeader().setSectionResizeMode(QHeaderView.ResizeToContents)    # 所有列都扩展自适应宽度，填充充满整个屏幕宽度

        # 设置列固定宽度
        self.list_model4.setHorizontalHeaderLabels(["ID", "试卷名称", "考试时长", "题目数量", "总分"])
        self.tableView.horizontalHeader().setSectionResizeMode(0, QHeaderView.Fixed)
        self.tableView.setColumnWidth(0, 50)
        # self.tableView.horizontalHeader().setSectionResizeMode(1, QHeaderView.Fixed)
        # self.tableView.setColumnWidth(1, 80)
        self.tableView.horizontalHeader().setSectionResizeMode(2, QHeaderView.Fixed)
        self.tableView.setColumnWidth(2, 65)
        self.tableView.horizontalHeader().setSectionResizeMode(3, QHeaderView.Fixed)
        self.tableView.setColumnWidth(3, 65)
        self.tableView.setSelectionBehavior(QAbstractItemView.SelectRows)  # 设置表格行选中模式
        self.tableView.verticalHeader().hide()

    def row_select(self, index):
        """
        list1行选中时刷新列表list2
        """
        self.listView_3.setCurrentIndex(QModelIndex())  # list3行设置不选中
        self.pushButton_2.setEnabled(False)
        self.pushButton.setEnabled(False)

        self.list_model2.clear()
        d = index.data()
        zhuanye = self.db.selectdata("select distinct 专业分类 from t_timu where 适用人员='"+str(d)+"'")
        list_item = []
        for x in zhuanye:
            list_item.append(QStandardItem(str(x[0])))
        self.list_model2.appendColumn(list_item)
        self.listView_2.setSelectionBehavior(QAbstractItemView.SelectRows)  # 设置表格的选取方式是行选取

    # todo 第二个list行选中
    def row_select1(self):
        self.listView_3.setCurrentIndex(QModelIndex())
        self.pushButton_2.setEnabled(False)

        if self.listView_2.currentIndex().row()>-1:
            self.pushButton.setEnabled(True)
        else:
            self.pushButton.setEnabled(False)

    # todo 第三个list行选中
    def row_select2(self):
        if self.listView_3.currentIndex().row() > -1:
            self.pushButton_2.setEnabled(True)
            self.pushButton_6.setEnabled(True)
        else:
            self.pushButton_2.setEnabled(False)
            self.pushButton_6.setEnabled(False)

    # todo 添加按钮
    def add_tiku(self):
        s = self.listView.currentIndex().data() + "-" + self.listView_2.currentIndex().data()

        # 判断是否在list3里边
        if not any([s == self.list_model3.data(self.list_model3.index(i, 0)) for i in range(self.list_model3.rowCount())]):
            self.list_model3.appendRow(QStandardItem(s))
        self.pushButton.setEnabled(False)

    # todo 删除按钮
    def delete_tiku(self):
        for index in self.listView_3.selectedIndexes():
            self.list_model3.removeRow(index.row())

        self.pushButton_2.setEnabled(False)

    def getExamnationDataFromList3(self):
        """
        根据用户在list3中添加题库情况，获取对应的题库数据
        """
        data = []
        for i in range(self.list_model3.rowCount()):
            a, b = str(self.list_model3.data(self.list_model3.index(i, 0))).split("-")
            sql = "select * from t_timu where 适用人员='"+a+"' and 专业分类='"+b+"'"
            data += self.db.selectdata(sql)
        return data

    # todo 模拟考试按钮,根据选择内容，选取所有考试题目
    def start_test(self):
        data = self.getExamnationDataFromList3()

        # 开始模拟考试
        if len(data) > 0:
            if QMessageBox.Yes == QMessageBox.question(self, "模拟练习", "是否开始模拟练习？"):
                self.win_kaoshi = moniPane(data, self.label_currentUser.text())
                self.win_kaoshi.setWindowModality(Qt.ApplicationModal)  # 设置为应用程序级别的模态
                self.win_kaoshi.show()
        else:
            QMessageBox.question(self, "提示", "请先添加题库信息！")

    # 我的错题按钮
    def wrong_test(self):
        # todo 错题考试，这里应该只存储错题题号，不存储其他内容
        data = self.db.selectdata("select * from t_wrongtiku where 用户名='"+self.userinfo[0][1]+"'")
        newdata = []
        for x in data:
            newdata.append((x[0],) + x[2:-2])
        if len(data) == 0:
            QMessageBox.about(self, "提示", "您当前还没有添加错题或当前没有错题！")
            return
        # 开始模拟考试
        if QMessageBox.Yes == QMessageBox.question(self, "错题练习", "是否开始错题练习？"):
            self.win_kaoshi = moniPane(newdata, self.userinfo[0][1])
            self.win_kaoshi.setWindowModality(Qt.ApplicationModal)  # 设置为应用程序级别的模态
            self.win_kaoshi.show()

    # 设置权限-用在show重载最后
    def setPriority(self):
        p = self.userinfo[0][8]
        if p == "一般用户":
            self.actionuser_guanli.setEnabled(False)
            self.actionuser_daochu.setEnabled(False)
            self.actionuser_daoru.setEnabled(False)

            self.action_shijuanguanli.setEnabled(False)
            self.action_daochutiku.setEnabled(False)
            self.actiond_daochushijuan.setEnabled(False)
            self.actiond_qingkongshijuan.setEnabled(False)
            self.action_qingkongtiku.setEnabled(False)
            self.action_tikuguanli.setEnabled(False)

    # todo 导出用户
    def userOutput(self):
        filepath, _ = QFileDialog.getSaveFileName(self, "导出用户信息到json文件", "./", "json文件(*.json)")
        if filepath == "":
            return
        try:
            data = self.db.selectdata("select * from t_userinfo")
            writejson(filepath, data)
            QMessageBox.about(self, "提示", "导出成功！")
        except Exception as e:
            QMessageBox.about(self, "提示", "导出失败！提示："+str(e))

    # todo 导入用户
    def userImport(self):
        filepath, _ = QFileDialog.getOpenFileName(self, "导入用户信息", "./", "json文件(*.json)")
        if filepath == "":
            return
        try:
            data = readjson(filepath)
            data1 = self.db.selectdata("select 用户名 from t_userinfo")
            for x in data:
                if (x[1],) in data1:
                    if QMessageBox.Yes == QMessageBox.question(self, "提示", "用户【" + str(x[1]) + "】已存在，是否覆盖？"):
                        self.db.excuteSql("delete from t_userinfo where 用户名='" + str(x[1]) + "'")
                        self.db.insert_singlerow_data("insert into t_userinfo(用户名,密码,姓名,性别,一级单位,二级单位,三级单位,用户权限,注册时间) values(?,?,?,?,?,?,?,?,?)", x[1:])
                else:
                    self.db.insert_singlerow_data("insert into t_userinfo(用户名,密码,姓名,性别,一级单位,二级单位,三级单位,用户权限,注册时间) values(?,?,?,?,?,?,?,?,?)", x[1:])
        except Exception as e:
            QMessageBox.about(self, "提示", "导入失败！提示："+str(e))

    # todo 用户管理菜单1
    def usermanage(self):
        self.userpane = userManegePane(self.userinfo)  # 把用户信息行传递过去
        self.userpane.setWindowModality(Qt.ApplicationModal)
        self.userpane.show()

    # todo 重载显示事件，获取在父程序中设置用户名信息?
    def showEvent(self, evt):
        super(MainPane, self).showEvent(evt)

        # 获取用户信息，后面用
        self.userinfo = self.db.selectdata("select * from t_userinfo where 用户名='" + self.label_currentUser.text() + "'")
        if len(self.userinfo) > 0:
            self.label_username.setText(self.userinfo[0][3])
            self.label_units1.setText(self.userinfo[0][5])
            self.label_units2.setText(self.userinfo[0][6])
            self.label_units3.setText(self.userinfo[0][7])

        self.setPriority()
        self.setuserIcon()

        # 设置时间显示
        t = time.strftime("%Y-%m-%d-%H-%M-%S", time.localtime(time.time()))
        self.label_currenttime.setText(t)
        # todo 上面的时间显示报错！！！

        self.timer = QTimer()
        self.timer.setInterval(1000)
        self.timer.start()
        self.timer.timeout.connect(self.refrush)

        # 设置试卷信息
        self.setdefautinfo()

    # todo 显示默认题库槽函数
    def setdefautinfo(self):
        self.label_default.setText("")
        self.label_14.setText("")
        self.label_testtime.setText("")
        self.label_nums.setText("")
        self.label_scores.setText("")
        self.label_score.setText("")
        self.label_scoretime.setText("")

        data = self.db.selectdata("select testinfo from t_setting where ID='1'")
        testinfo = self.db.selectdata("select * from t_shijuan where ID='{}'".format(data[0][0]))
        for x in testinfo:
            testname = x[3]
            testtime = x[8]
            nums = str(x[4]).split(",")
            vals = str(x[5]).split(",")
            score, sums = 0, 0
            for i in range(5):
                sums += int(nums[i])
                score += int(nums[i]) * int(vals[i])
            self.label_default.setText(str(testname))
            self.label_14.setText(str(testname))
            self.label_testtime.setText(str(testtime))
            self.label_nums.setText(str(sums))
            self.label_scores.setText(str(score))
            self.label_score.setText("")
            self.label_scoretime.setText("")

    # todo 时间刷新
    def refrush(self):
        t = time.strftime("%Y-%m-%d-%H-%M-%S", time.localtime(time.time()))
        self.label_currenttime.setText(t)

    # 试卷管理菜单
    def papermanagement(self):
        data = self.db.selectdata("select * from t_shijuan")
        if len(data) == 0:
            QMessageBox.about(self, "提示", "试卷信息为空，请先添加试卷！")
        else:
            self.ma = paperManagePane(self)
            self.ma.setdefault_signal.connect(self.setdefautinfo)
            self.ma.delete_testpaper.connect(self.signal_delete_test)
            self.ma.open()

    def signal_delete_test(self):
        self.setdefautinfo()
        self.frushlist4()

    # 开始考试菜单
    def on_start(self):
        # 获取默认设置的题库
        data = self.db.selectdata("select testinfo from t_setting")
        if data[0][0] == "":
            QMessageBox.about(self, "提示", "请先添加试卷！")
        else:
            dt = self.db.selectdata("select * from t_shijuan where ID='{}'".format(data[0][0]))
            if len(dt) > 0:
                self.startTest(dt)
            else:
                QMessageBox.about(self, "提示", "试卷不存在或已删除，请重新添加！")

    # todo 考试函数
    def startTest(self, data, str_test = "顺序考试"):
        if len(data) > 0:
            s = "即将开始【" + str(data[0][3]) + "】考试，考试时长" + str(data[0][8]) + "分钟，是否开始？"
            if QMessageBox.Yes == QMessageBox.question(self, "开始考试", s):
                self.win_kaoshi = testPane(data, self.label_currentUser.text(), str_test)
                self.win_kaoshi.setWindowTitle("正在进行【" + str(data[0][3]) + "】考试")
                # self.win_kaoshi.suiji = str_test
                self.win_kaoshi.setWindowModality(Qt.ApplicationModal)  # 设置为应用程序级别的模态
                self.win_kaoshi.show()
                self.win_kaoshi.test_finished_signal.connect(self.showtestresult)
        else:
            QMessageBox.about(self, "提示", "试卷不存在，请重新设置！")
            self.setdefautinfo()

    # todo 显示考试结果槽函数
    def showtestresult(self, list_result):
        self.label_score.setText(str(list_result[-1]))
        self.label_scoretime.setText(str(list_result[0]))

    # todo 导出Excel题库菜单
    def tk_output(self):
        filepath, _ = QFileDialog.getSaveFileName(self, "导出题库到Excel文件", "./", "excel(*.xlsx)")
        if not os.path.isfile(filepath):
            print("取消，不操作")
            return
        try:
            data = self.db.selectdata("select * from t_timu")
            if writeToExcel(filepath, data):
                QMessageBox.about(self, "提示", "导出题库成功！")
        except Exception as e:
            QMessageBox.about(self, "提示", "导出失败！提示信息：" + str(e))

    # todo 选择导入的文件,导入
    def choosefile(self):
        flag1 = self.d.checkBox.isChecked()
        flag2 = self.d.checkBox_2.isChecked()
        self.dialog.close()

        file, _ = QFileDialog.getOpenFileName(self, "选择题库", "./", "All(*.*);;excel(*.xlsx)", "excel(*.xlsx)")
        try:
            new_tiku = importSubjectsFromExcelFile(file)
            if flag2:  # 清空题库，重新添加
                self.db.excuteSql("delete from t_timu")

            # 根据题库长度，导入数据,注意选项一般不超过10个
            sql = "insert into t_timu(适用人员, 专业分类, 题干, 题型," \
                  "选项1, 选项2, 选项3, 选项4, 选项5, 选项6, 选项7, 选项8,选项9,选项10," \
                  "答案,备注) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
            list_tianchong = [("",), ("", ""), ("", "", ""), ("", "", "", ""), ("", "", "", "", ""),
                              ("", "", "", "", "", ""), ("", "", "", "", "", "", ""), ("", "", "", "", "", "", "", ""),
                              ("", "", "", "", "", "", "", "", ""), ("", "", "", "", "", "", "", "", "", "")]
            for x in new_tiku:
                if 15 - len(x) > 0:
                    val = x[:-2] + list_tianchong[15 - len(x)] + x[-2:]
                    if flag1:  # 附加题库
                        tmp = self.db.selectdata("select * from t_timu where 题干='" + x[2] + "'")
                        if len(tmp) == 0:
                            self.db.insert_singlerow_data(sql, val)
                    else:
                        self.db.insert_singlerow_data(sql, val)
            QMessageBox.about(self, "提示", "导入成功！")
            self.initList()  # 刷新题库信息,存入文件
        except Exception as e:
            print(e)
            if str(e).startswith("openpyxl does not support"):
                QMessageBox.about(self, "提示", "文件名不合法！")

    # todo 导入题库菜单
    def importTk(self):
        # 导入题库，构建一个模对话框
        self.dialog = QDialog(self)
        self.dialog.on_clicked = self.choosefile
        self.dialog.on_enable = self.enabel_choose
        self.dialog.setWindowTitle("选择题库文件（*.xlsx)")
        self.d = Ui_Dialog()
        self.d.setupUi(self.dialog)
        self.dialog.open()

    # todo 使模对话框选择文件按钮可用
    def enabel_choose(self):
        self.d.pushButton.setEnabled(True)

    # todo 添加试卷菜单
    def paperset(self):
        self.papersetUI = PapersetPane(self)
        self.papersetUI.add_test_signal.connect(self.frushlist4)
        self.papersetUI.open()

    # todo 清空试卷菜单
    def clearSJ(self):
        if QMessageBox.Yes == QMessageBox.question(self, "提示", "确定清空所有试卷？清空后不可恢复！"):
            self.db.excuteSql("delete from t_shijuan")
            self.setdefautinfo()
            self.frushlist4()

if __name__ == '__main__':
    import sys
    app = QApplication(sys.argv)
    window = MainPane()
    window.label_currentUser.setText("dll")
    window.show()
    sys.exit(app.exec_())